package com.sharemarking.wa.platform.mapper;

import com.sharemarking.wa.platform.dto.monthlyStatement.MonthlyStatementListDto;
import com.sharemarking.wa.platform.dto.monthlyStatement.StatementListDto;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author dxy
 */
@Repository
public interface MonthlyStatementMapper {

    @Select("SELECT DATE_FORMAT( a.create_date, '%Y年%m月' ) AS time,COUNT(*) AS total,SUM(b.amount) AS amount\n" +
            "FROM sys_invoice a LEFT JOIN sys_pay_record b ON a.transaction_code = b.transaction_code \n" +
            "WHERE a.member_id = #{memberId} GROUP BY DATE_FORMAT( a.create_date, '%Y-%m' ) LIMIT #{pageIndex},#{pageSize}")
    List<MonthlyStatementListDto> getList(Map map);

    @Select("SELECT COUNT(*) FROM (SELECT COUNT(*)" +
            "FROM sys_invoice a LEFT JOIN sys_pay_record b ON a.transaction_code = b.transaction_code \n" +
            "WHERE a.member_id = #{memberId} GROUP BY DATE_FORMAT( a.create_date, '%Y-%m' ))q")
    Integer getListTotal(Map map);

    @Select("SELECT b.amount,a.transaction_code AS code,a.create_date,a.description " +
            "FROM sys_invoice a LEFT JOIN sys_pay_record b  ON a.transaction_code = b.transaction_code  WHERE DATE_FORMAT(a.create_date, '%Y') = DATE_FORMAT(#{time}, '%Y') AND DATE_FORMAT(a.create_date, '%m') = DATE_FORMAT(#{time}, '%m') AND a.member_id = #{memberId} " +
            "LIMIT #{pageIndex},#{pageSize}")
    List<StatementListDto> getMonthList(Map map);

    @Select("SELECT COUNT(*) FROM sys_invoice a WHERE DATE_FORMAT( a.create_date, '%Y' ) = DATE_FORMAT(#{time}, '%Y') AND DATE_FORMAT(a.create_date, '%m') = DATE_FORMAT(#{time}, '%m') AND a.member_id = #{memberId}")
    Integer getMonthListTotal(Map map);

    @Select("SELECT b.is_monthly_pay FROM sys_org_member a LEFT JOIN sys_org b ON a.org_id = b.id WHERE a.member_id = #{memberId}")
    Integer isMonthlyPay(Integer memberId);

    @Select("SELECT b.amount,a.transaction_code AS code,a.create_date, " +
            "(SELECT CONCAT ('废品运输，',CONCAT(c.province,c.city,c.area,c.city,c.address) ,'到',CONCAT(b.province,b.city,b.area,b.city,b.address))  FROM wa_order a LEFT JOIN wa_order_address b ON a.id = b.order_id  \n" +
            " LEFT JOIN wa_order_address c ON a.parent_id = c.order_id \n" +
            "WHERE a.code = a.transaction_code AND b.type = 1 AND c.type = 0)AS description\n" +
            "FROM sys_invoice a LEFT JOIN sys_pay_record b  ON a.transaction_code = b.transaction_code  WHERE a.member_id = #{memberId} LIMIT #{pageIndex},#{pageSize}")
    List<StatementListDto> getPCMonthList(Map map);

    @Select("SELECT COUNT(*) FROM sys_invoice a WHERE a.member_id = #{memberId}")
    Integer getPCMonthListTotal(Map map);
}
